import { parseExcelBuffer } from './excel-parser.server';
import fs from 'fs';
import { classificationDAO, attributeDAO, entityDAO, parameterMappingDAO, initializeDatabase } from './database';
import type { ClassificationRecord, AttributeRecord, EntityRecord, ParameterMappingRecord } from './database';

interface ImportStats {
  classifications: number;
  attributes: number;
  entities: number;
  parameterMappings: number;
}

// 专业关键词映射
const SPECIALTY_KEYWORDS = {
  electrical: ['电', '灯', '开关', '配电', '电缆', '电气', '照明', '插座'],
  hvac: ['暖', '通', '空调', '风', '温', '湿', '制冷', '热'],
  communication: ['通信', '网络', '电话', '传输', '数据', '信号'],
  instrumentation: ['仪表', '仪器', '测量', '监控', '传感', '控制', '自动']
};

export class DataImporter {
  private stats: ImportStats = {
    classifications: 0,
    attributes: 0,
    entities: 0,
    parameterMappings: 0
  };

  // 导入01015分类标准数据
  async importClassificationData(filename: string): Promise<void> {
    console.log(`正在导入01015分类标准数据: ${filename}`);
    
    try {
      const filePath = `excel/${filename}`;
      const buffer = fs.readFileSync(filePath);
      const excelData = parseExcelBuffer(buffer.buffer, filename);
      
      // 初始化数据库
      initializeDatabase();
      
      // 处理每个工作表
      for (const sheet of excelData.sheets) {
        if (sheet.sheetName.includes('Sheet') || sheet.sheetName.includes('说明')) {
          continue; // 跳过无关工作表
        }
        
        await this.processClassificationSheet(sheet, filename);
        
        // 如果是C开头的表，处理属性数据
        if (sheet.sheetName.startsWith('C')) {
          await this.processAttributeSheet(sheet, filename);
        }
      }
      
      console.log(`分类标准数据导入完成: ${JSON.stringify(this.stats)}`);
    } catch (error) {
      console.error('导入分类标准数据失败:', error);
      throw error;
    }
  }

  // 导入实体对象数据
  async importEntityData(filename: string): Promise<void> {
    console.log(`正在导入实体对象数据: ${filename}`);
    
    try {
      const filePath = `excel/${filename}`;
      const buffer = fs.readFileSync(filePath);
      const excelData = parseExcelBuffer(buffer.buffer, filename);
      
      // 处理每个工作表
      for (const sheet of excelData.sheets) {
        await this.processEntitySheet(sheet);
      }
      
      console.log(`实体对象数据导入完成: ${JSON.stringify(this.stats)}`);
    } catch (error) {
      console.error('导入实体对象数据失败:', error);
      throw error;
    }
  }

  // 处理分类标准工作表
  private async processClassificationSheet(sheet: any, filename: string): Promise<void> {
    if (!sheet.rows || sheet.rows.length === 0) return;
    
    const headers = sheet.headers;
    
    // 查找关键列
    const codeIndex = this.findColumnIndex(headers, ['代码', 'code', '编码']);
    const nameIndex = this.findColumnIndex(headers, ['名称', 'name', '分类名称']);
    const levelIndex = this.findColumnIndex(headers, ['级别', 'level', '层级']);
    const parentIndex = this.findColumnIndex(headers, ['上级代码', 'parent', '父代码']);
    const descIndex = this.findColumnIndex(headers, ['描述', 'description', '说明']);
    
    if (codeIndex === -1 || nameIndex === -1) {
      console.log(`工作表 ${sheet.sheetName} 未找到必要的列，跳过处理`);
      return;
    }
    
    // 处理数据行
    for (let i = 0; i < sheet.rows.length; i++) {
      const row = sheet.rows[i];
      if (!row || !row[codeIndex] || !row[nameIndex]) continue;
      
      const record: ClassificationRecord = {
        code: String(row[codeIndex]).trim(),
        name: String(row[nameIndex]).trim(),
        level: levelIndex >= 0 ? String(row[levelIndex] || '').trim() : '1',
        parent_code: parentIndex >= 0 ? String(row[parentIndex] || '').trim() || undefined : undefined,
        description: descIndex >= 0 ? String(row[descIndex] || '').trim() || undefined : undefined,
        sheet_name: sheet.sheetName
      };
      
      classificationDAO.insert(record);
      this.stats.classifications++;
    }
  }

  // 处理属性工作表
  private async processAttributeSheet(sheet: any, filename: string): Promise<void> {
    if (!sheet.rows || sheet.rows.length === 0) return;
    
    const headers = sheet.headers;
    
    // 查找关键列
    const nameIndex = this.findColumnIndex(headers, ['属性名称', 'attribute', '字段名']);
    const typeIndex = this.findColumnIndex(headers, ['类型', 'type', '数据类型']);
    const requiredIndex = this.findColumnIndex(headers, ['必填', 'required', '是否必填']);
    const defaultIndex = this.findColumnIndex(headers, ['默认值', 'default', '缺省值']);
    const descIndex = this.findColumnIndex(headers, ['描述', 'description', '说明']);
    
    if (nameIndex === -1) {
      console.log(`属性表 ${sheet.sheetName} 未找到属性名称列，跳过处理`);
      return;
    }
    
    // 从工作表名称获取分类代码
    const classificationCode = this.extractClassificationCode(sheet.sheetName);
    if (!classificationCode) {
      console.log(`无法从工作表名 ${sheet.sheetName} 提取分类代码`);
      return;
    }
    
    // 处理数据行
    for (let i = 0; i < sheet.rows.length; i++) {
      const row = sheet.rows[i];
      if (!row || !row[nameIndex]) continue;
      
      const record: AttributeRecord = {
        classification_code: classificationCode,
        attribute_name: String(row[nameIndex]).trim(),
        attribute_type: typeIndex >= 0 ? String(row[typeIndex] || 'string').trim() : 'string',
        is_required: requiredIndex >= 0 ? this.parseBoolean(row[requiredIndex]) : false,
        default_value: defaultIndex >= 0 ? String(row[defaultIndex] || '').trim() || undefined : undefined,
        description: descIndex >= 0 ? String(row[descIndex] || '').trim() || undefined : undefined,
        sheet_name: sheet.sheetName
      };
      
      attributeDAO.insert(record);
      this.stats.attributes++;
    }
  }

  // 处理实体对象工作表
  private async processEntitySheet(sheet: any): Promise<void> {
    if (!sheet.rows || sheet.rows.length === 0) return;
    
    const headers = sheet.headers;
    
    // 查找关键列
    const nameIndex = this.findColumnIndex(headers, ['实体对象名称', '设备表及材料表中的实体对象名称', '名称']);
    const codeIndex = this.findColumnIndex(headers, ['设备编码', '编码', 'code']);
    const categoryIndex = this.findColumnIndex(headers, ['分类', 'category', '类别']);
    const standardNameIndex = this.findColumnIndex(headers, ['标准名称', '01015分类表中的实体对象名称']);
    const standardCodeIndex = this.findColumnIndex(headers, ['标准代码', '01015分类表中的代码']);
    
    if (nameIndex === -1) {
      console.log(`工作表 ${sheet.sheetName} 未找到实体对象名称列，跳过处理`);
      return;
    }
    
    // 处理数据行
    for (let i = 0; i < sheet.rows.length; i++) {
      const row = sheet.rows[i];
      if (!row || !row[nameIndex]) continue;
      
      const name = String(row[nameIndex]).trim();
      const specialty = this.determineSpecialty(name);
      
      const record: EntityRecord = {
        name: name,
        code: codeIndex >= 0 ? String(row[codeIndex] || '').trim() || undefined : undefined,
        category: categoryIndex >= 0 ? String(row[categoryIndex] || '').trim() || undefined : undefined,
        standard_name: standardNameIndex >= 0 ? String(row[standardNameIndex] || '').trim() || undefined : undefined,
        standard_code: standardCodeIndex >= 0 ? String(row[standardCodeIndex] || '').trim() || undefined : undefined,
        specialty: specialty
      };
      
      const entityId = entityDAO.insert(record);
      this.stats.entities++;
      
      // 生成参数映射
      await this.generateParameterMappings(entityId, record);
    }
  }

  // 生成参数映射
  private async generateParameterMappings(entityId: number, entity: EntityRecord): Promise<void> {
    const mappings: ParameterMappingRecord[] = [];
    
    // 命名参数
    mappings.push({
      entity_id: entityId,
      classification_code: entity.standard_code || '',
      parameter_type: 'naming',
      parameter_name: '对象名称',
      parameter_value: entity.name
    });
    
    if (entity.code) {
      mappings.push({
        entity_id: entityId,
        classification_code: entity.standard_code || '',
        parameter_type: 'naming',
        parameter_name: '对象编码',
        parameter_value: entity.code
      });
    }
    
    if (entity.standard_code) {
      mappings.push({
        entity_id: entityId,
        classification_code: entity.standard_code || '',
        parameter_type: 'naming',
        parameter_name: '标准代码',
        parameter_value: entity.standard_code
      });
    }
    
    // 位置信息参数（基于专业生成默认值）
    const locationParams = this.generateLocationParameters(entity.specialty);
    for (const [key, value] of Object.entries(locationParams)) {
      mappings.push({
        entity_id: entityId,
        classification_code: entity.standard_code || '',
        parameter_type: 'location',
        parameter_name: key,
        parameter_value: value
      });
    }
    
    // 基础参数（基于专业生成默认值）
    const basicParams = this.generateBasicParameters(entity.specialty);
    for (const [key, value] of Object.entries(basicParams)) {
      mappings.push({
        entity_id: entityId,
        classification_code: entity.standard_code || '',
        parameter_type: 'basic',
        parameter_name: key,
        parameter_value: value
      });
    }
    
    // 批量插入映射
    for (const mapping of mappings) {
      parameterMappingDAO.insert(mapping);
      this.stats.parameterMappings++;
    }
  }

  // 工具方法
  private findColumnIndex(headers: any[], possibleNames: string[]): number {
    for (const name of possibleNames) {
      const index = headers.findIndex(h => h && String(h).includes(name));
      if (index >= 0) return index;
    }
    return -1;
  }

  private extractClassificationCode(sheetName: string): string | null {
    // 从工作表名称提取分类代码，如 "C01.02.03" 
    const match = sheetName.match(/C[\d.]+/);
    return match ? match[0] : null;
  }

  private parseBoolean(value: any): boolean {
    if (typeof value === 'boolean') return value;
    if (typeof value === 'string') {
      const lower = value.toLowerCase();
      return lower === 'true' || lower === '是' || lower === 'yes' || lower === '1';
    }
    if (typeof value === 'number') return value !== 0;
    return false;
  }

  private determineSpecialty(name: string): string {
    for (const [specialty, keywords] of Object.entries(SPECIALTY_KEYWORDS)) {
      if (keywords.some(keyword => name.includes(keyword))) {
        return specialty;
      }
    }
    return 'electrical'; // 默认分类
  }

  private generateLocationParameters(specialty: string): Record<string, string> {
    const base = {
      '建筑物': '主厂房',
      '楼层': '1层',
      '房间': '设备间',
      '具体位置': '待确定'
    };
    
    switch (specialty) {
      case 'electrical':
        return { ...base, '配电室': '主配电室', '回路': '1#回路' };
      case 'hvac':
        return { ...base, '系统': '暖通系统', '区域': '主要区域' };
      case 'communication':
        return { ...base, '机房': '通信机房', '机柜': '1#机柜' };
      case 'instrumentation':
        return { ...base, '控制室': '中控室', '回路': '仪表回路' };
      default:
        return base;
    }
  }

  private generateBasicParameters(specialty: string): Record<string, string> {
    const base = {
      '制造商': '待确定',
      '型号': '待确定',
      '规格': '待确定',
      '安装日期': '待确定'
    };
    
    switch (specialty) {
      case 'electrical':
        return { ...base, '额定电压': '380V', '额定电流': '待确定', '防护等级': 'IP54' };
      case 'hvac':
        return { ...base, '制冷量': '待确定', '功率': '待确定', '风量': '待确定' };
      case 'communication':
        return { ...base, '传输速率': '1000Mbps', '接口类型': 'RJ45', '协议': 'TCP/IP' };
      case 'instrumentation':
        return { ...base, '测量范围': '待确定', '精度': '±0.5%', '信号类型': '4-20mA' };
      default:
        return base;
    }
  }

  // 获取导入统计
  getStats(): ImportStats {
    return { ...this.stats };
  }

  // 重置统计
  resetStats(): void {
    this.stats = {
      classifications: 0,
      attributes: 0,
      entities: 0,
      parameterMappings: 0
    };
  }
}

// 导出单例
export const dataImporter = new DataImporter(); 